using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class EmployeeDAO
    {
        #region 1. Inserting
        public static bool InsertEmployee(EmployeeDTO emp)
        {
            bool result=false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@EmployeeID", emp.EmployeeID));
                sqlParams.Add(new SqlParameter("@Name", emp.Name));
                sqlParams.Add(new SqlParameter("@Email", emp.Email));
                sqlParams.Add(new SqlParameter("@Salary", emp.Salary));
                sqlParams.Add(new SqlParameter("@EmployeeStyle", emp.EmployeeStyle));
                // Call Store Procedure
               
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spInsertEmployee", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
#endregion
        #region 2. Updating
        public static bool UpdateEmployee(EmployeeDTO emp)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@EmployeeID", emp.EmployeeID));
                sqlParams.Add(new SqlParameter("@Name", emp.Name));
                sqlParams.Add(new SqlParameter("@Email", emp.Email));
                sqlParams.Add(new SqlParameter("@Salary", emp.Salary));
                sqlParams.Add(new SqlParameter("@EmployeeStyle", emp.EmployeeStyle));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spUpdateEmployee", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
#endregion
        #region 3. Deleting
        public static bool DeleteEmployeeByID(String employeeID)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@EmployeeID", employeeID));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spDeleteEmployeeByID", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region 4. Retrieving
        public static List<EmployeeDTO> SelectEmployeeByEmployeeStyle(int employeeStyle)
        {
            List<EmployeeDTO> list = new List<EmployeeDTO>();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@EmployeeStyle", employeeStyle));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectEmployeeByEmployeeStyle", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    EmployeeDTO emp = new EmployeeDTO();
                    emp.EmployeeID = dr["EmployeeID"].ToString();
                    emp.Name = dr["Name"].ToString();
                    emp.Email = dr["Email"].ToString();
                    emp.Salary = float.Parse(dr["Salary"].ToString ());
                    emp.EmployeeStyle = int.Parse(dr["EmployeeStyle"].ToString ());
                    list.Add(emp);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public static List<EmployeeDTO> SelectEmployeeAll()
        {
            List<EmployeeDTO> list = new List<EmployeeDTO>();
            try
            {
               DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectEmployeeAll");
                foreach (DataRow dr in dt.Rows)
                {
                    EmployeeDTO emp = new EmployeeDTO();
                    emp.EmployeeID = dr["EmployeeID"].ToString();
                    emp.Name = dr["Name"].ToString();
                    emp.Email = dr["Email"].ToString();
                    emp.Salary = float.Parse(dr["Salary"].ToString());
                    emp.EmployeeStyle = int.Parse(dr["EmployeeStyle"].ToString());
                    list.Add(emp);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public static EmployeeDTO SelectEmployeeByID(String employeeID)
        {
            EmployeeDTO emp = new EmployeeDTO();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@EmployeeID", employeeID));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectEmployeeByID", sqlParams);
                DataRow dr = dt.Rows[0];                                   
                emp.EmployeeID = dr["EmployeeID"].ToString();
                emp.Name = dr["Name"].ToString();
                emp.Email = dr["Email"].ToString();
                emp.Salary = float.Parse(dr["Salary"].ToString());
                emp.EmployeeStyle = int.Parse(dr["EmployeeStyle"].ToString());  
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return emp;
        }
        public static bool CheckEmployeeByID(String employeeID)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@EmployeeID", employeeID));

                DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectEmployeeByID", sqlParams);
                if (dt.Rows.Count == 1)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
    }
}
